<?php
namespace app\common\lib;

use think\Config;
use think\Db;
/**
 * 执行sql文件
 */
class SqlEasy
{
    public function index()
    {
        $res = Db::query('SELECT version();');
        dump($res[0]['version()']);
        $version = $res[0]['version()'];
        dump( $version > '4.1' );
    }

    /**
     * 执行mysql.sql文件
     */
    public function sqlExecute( $sql )
    {
        $result = true;
        $sqls = $this->sqlSplit( $sql );
        if( is_array($sqls) ){
            foreach ($sqls as $sql) {
                preg_match_all("/[\/\*|\*\/]/i", $sql, $matches);
                if (trim($sql) != '' && empty( $matches[0] ) ) {
                    try {
                        Db::execute ( $sql ) ;
                    } catch ( \Exception $e) {
                        $result = [ 'error' => '100' , 'message' => $e->getMessage() ];
                        break;
                    }
                }
            }
        }
        return $result;
    }

    /**
     * 处理sql语句
     */
    private function sqlSplit( $sql = '' )
    {
        // 数据库信息
        $dbInfo = array(
            'charset'   => Config('database.charset')
        );
        $version = Db::query('SELECT version();');
        $version = isset($version[0]['version()']) ? $version[0]['version()'] : '1' ;
        if( $version > '4.1' && $dbInfo['charset'] ) {
            $sql = preg_replace("/TYPE=(InnoDB|MyISAM|MEMORY)( DEFAULT CHARSET=[^; ]+)?/", "ENGINE=\\1 DEFAULT CHARSET=".$dbInfo['charset'], $sql);
        }
        $queriesarray = explode ( ";", trim($sql) );
        $ret = [];
        $num = 0;
        unset( $sql );
        foreach ($queriesarray as $query) {
            $ret[$num] = '';
            $queries = explode("\n", trim($query));
            $queries = array_filter($queries);
            foreach ($queries as $query) {
                $str1 = substr($query, 0, 1);
                if($str1 != '#' && $str1 != '-' ){
                    $ret[$num] .= $query;
                } 
            }
            $num++;
        }
        return array_filter( $ret );
    }
}

// need open function of shell_exec  , so discard
class SomeOne
{
    public function __construct () {
        
    }
    public function execute_sql($file) {
        /* Define variables here */
        $dbc = array(
            'hostname' => Config('database.hostname'),
            'username' => Config('database.username'),
            'password' => Config('database.password'),
            'database' =>  Config('database.database')
        );
        //1st method; directly via mysql
        $mysql_paths = array();
        //use mysql location from `which` command.
        $mysql = trim(`which mysql`);
        if (is_executable($mysql)) {
            array_unshift($mysql_paths, $mysql);
        }
        //Default paths
        $mysql_paths[] = '/Applications/MAMP/Library/bin/mysql';  //Mac Mamp
        $mysql_paths[] = 'c:\xampp\mysql\bin\mysql.exe'; //XAMPP
        $mysql_paths[] = '/usr/bin/mysql';  //Linux
        $mysql_paths[] = '/usr/local/mysql/bin/mysql'; //Mac
        $mysql_paths[] = '/usr/local/bin/mysql'; //Linux
        $mysql_paths[] = '/usr/mysql/bin/mysql'; //Linux
        $database =     escapeshellarg($dbc['database']);
        $db_hostname = escapeshellarg($dbc['hostname']);
        $db_username = escapeshellarg($dbc['username']);
        $db_password = escapeshellarg($dbc['password']);
        $file_to_execute = escapeshellarg($file);
        foreach ($mysql_paths as $mysql) {
            if (is_executable($mysql)) {
                $execute_command = "\"$mysql\" --host=$db_hostname --user=$db_username --password=$db_password $database < $file_to_execute";
                $status = false;
                system($execute_command, $status);
                return $status == 0;
            }
        }

        if (isset($this->db) && isset($this->db->dbdriver) && $this->db->dbdriver == 'mysqli') {
            //2nd method; using mysqli
            mysqli_multi_query($this->db->conn_id, file_get_contents($file));
            //Make sure this keeps php waiting for queries to be done
            do {

            } while (mysqli_more_results($this->db->conn_id) && mysqli_next_result($this->db->conn_id));
            return TRUE;
        }

        //3rd Method Use PDO as command. See http://stackoverflow.com/a/6461110/627473
        //Needs php 5.3, mysqlnd driver
        $mysqlnd = function_exists('mysqli_fetch_all');

        if ($mysqlnd && version_compare(PHP_VERSION, '5.3.0') >= 0) {
            $database = $dbc['database'];
            $db_hostname = $dbc['hostname'];
            $db_username = $dbc['username'];
            $db_password = $dbc['password'];
            $dsn = "mysql:dbname=$database;host=$db_hostname";
            $db = new PDO($dsn, $db_username, $db_password);
            $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);
            $sql = file_get_contents($file);
            $db->exec($sql);

            return TRUE;
        }

        return FALSE;
    }
}

